package com.gugee.ins.common.dal.mapper.media;

import com.gugee.common.mapper.GugeeMapper;
import com.gugee.ins.common.dto.InsAdMediaStInfoDto;
import com.gugee.ins.common.dto.InsMediaScoreDto;
import com.gugee.ins.common.model.es.media.InsMediaIndex;
import com.gugee.ins.common.model.mysql.media.InsMedia;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.annotations.Update;

import java.util.List;

public interface InsMediaMapper extends GugeeMapper<InsMedia> {
    /**
     * 根据 uid 和 时间 查找数据
     * @param uid
     * @param timeLimit
     * @param commentMinCount
     * @return
     */
    @Select("select id,owner_uid,shortcode,collect_comments_count,last_comment_collect_time,comment_count " +
            "from ins_media t where t.owner_uid = #{uid} and t.taken_time > #{timeLimit} and t.comments_disabled = 0 " +
            " and t.comment_count > #{commentMinCount} order by taken_time desc ")
    List<InsMedia> findByOwnerUidAndTakenTime(@Param("uid") long uid,@Param("timeLimit") long timeLimit
            ,@Param("commentMinCount") int commentMinCount);


    @Select("select m.* from ins_media m where m.owner_uid = #{uid} and m.taken_time > #{timeLimit} ")
    List<InsMediaIndex> findInsMediaIndexByOwnerUidAndTakenTime(@Param("uid") long uid,@Param("timeLimit") long timeLimit);

    /**
     * 统计帖子数
     * @param uid
     * @param timeLimit
     * @param isAd
     * @return
     */
    @Select({"<script>",
            "select count(*) from ins_media where owner_uid = #{uid} and taken_time > #{timeLimit} ",
            "<when test='null!=isAd'> ",
            "AND is_ad = #{isAd} ",
            "</when>",
            "</script>"})
    long countByOwnerUidAndTakenTimeAndAdIs(long uid,long timeLimit, Integer isAd);

    /**
     * 统计参与率
     * @param uid
     * @param timeLimit
     * @param isAd
     * @return
     */
    @Select({"<script>",
            "select IFNULL(SUM(er),0) from ins_media where owner_uid = #{uid} and taken_time > #{timeLimit} ",
            "<when test='null!=isAd'> ",
            "AND is_ad = #{isAd} ",
            "</when>",
            "</script>"})
    double sumERByOwnerUidAndTakenTimeAndAdIs(long uid,long timeLimit, Integer isAd);


    /**
     * 平均参与率
     * @param uid
     * @param timeLimit
     * @param isAd
     * @return
     */
    @Select({"<script>",
            "select IFNULL(AVG(er),0) from ins_media where owner_uid = #{uid} and taken_time > #{timeLimit} ",
            "<when test='null!=isAd'> ",
            "AND is_ad = #{isAd} ",
            "</when>",
            "</script>"})
    double avgERByOwnerUidAndTakenTimeAndAdIs(long uid,long timeLimit, Boolean isAd);

    /**
     * 更新帖子记录中评论相关字段
     * @param shortcode
     * @param collectCommentCount
     * @param time
     */
    @Update("update `ins_media` set collect_comments_count = collect_comments_count + #{collectCommentCount},last_comment_collect_time = #{last_comment_collect_time} where shortcode = #{shortcode} ")
    void updateMediaCommentInfo(@Param("collectCommentCount") long collectCommentCount, @Param("last_comment_collect_time")long time,@Param("shortcode") String shortcode);

    /**
     * 更新帖子是否是广告帖
     * @param shortcode
     * @param isAd
     */
    @Update("update `ins_media` set is_ad = #{isAd} where shortcode = #{shortcode} ")
    void updateMediaIsAd(@Param("isAd")int isAd,@Param("shortcode")String shortcode);

    /**
     * 根据yid 获取标题文本
     * @param uid
     * @param limitMinLength
     * @return
     */
    @Select("select caption from ins_media where owner_uid = #{uid} and CHAR_LENGTH(caption) > #{limitMinLength} ")
    List<String> findCaptionByUid(long uid,int limitMinLength);

    /**
     * 根据uid 获取需要进行广告帖识别的shortcode
     * @param uid
     * @param limitTime
     * @return
     */
    @Select("select t.shortcode from ins_media t " +
        " where t.owner_uid = #{uid} and t.taken_time > #{limitTime} ")
    List<String> findAdMediaIdentifyShortCodeByUid(long uid,long limitTime);


    /**
     * 广告帖子占总帖子比例
     * @param uid
     * @param limitTime
     * @return
     */
    @Select("select IFNULL(" +
            "(select count(*) from ins_media where owner_uid =${uid} and taken_time > #{limitTime} and is_ad = 1)" +
            "/" +
            "(select count(*) from ins_media where owner_uid =${uid} and taken_time > #{limitTime}),0)")
    double getAdMediaRate(long uid,long limitTime);

    /**
     * 近30天帖子总数
     * @param uid
     * @param timeLimit
     * @return
     */
    @Select("select count(1) total from ins_media where owner_uid = #{uid} and taken_time > #{timeLimit} ")
    int getMediaTotal(long uid,long timeLimit);

    /**
     * 广告帖子分类统计信息
     * @param uid
     * @param timeLimit
     * @return
     */
    @Select("select is_video,IFNULL(avg(er),0) ad_er,count(1) total,IFNULL(round(avg(video_view_count)),0) video_view_count from ins_media " +
            " where owner_uid = #{uid} and taken_time > #{timeLimit} and is_ad = 1 group by is_video")
    List<InsAdMediaStInfoDto> getAdMediaInfoByUid(long uid, long timeLimit);

    /**
     * 近一周广告帖子统计信息
     * @param uid
     * @param timeLimit
     * @return
     */
    @Select("select IFNULL(avg(er),0) ad_er,count(1) total from ins_media " +
            " where owner_uid = #{uid} and taken_time > #{timeLimit} and is_ad = 1 ")
    InsAdMediaStInfoDto getAdMediaInfoForWeekByUid(long uid, long timeLimit);

    /**
     * 根据shortcode 更新带货状态
     * @param shortcode
     */
    @Update("update `ins_media` t1, " +
            " (select avg(distinct is_self)*2 ptt from ins_rel_media_product where shortcode = #{shortcode}) t2 " +
            " set product_tag_type = (case when t2.ptt = 0 then 2 when t2.ptt = 1 then 3 when t2.ptt = 2 then 1 else 0 end) " +
            " where shortcode = #{shortcode} ")
    void updateProductTagType(@Param("shortcode")String shortcode);

    /**
     * 根据uid 和 时间区间获取需要计算分数的近期帖子列表
     * @param uid
     * @param limitTime
     * @return
     */
    @Select("select id ,mid,shortcode,comment_count,like_count from ins_media where owner_uid = #{uid} and taken_time > #{limitTime} ")
    List<InsMediaScoreDto> findAnalysisScoreMediaByUid(long uid, long limitTime);
}